﻿using GoodDotNet.Core.Data;
using GoodDotNet.Core.Entity;
using GoodDotNet.Core.IRepository;
using GoodDotNet.Core.Model;
using GoodDotNet.Data.Models;
using Microsoft.EntityFrameworkCore;
using MySql.Data.MySqlClient;
using System.Data.Common;

namespace GoodDotNet.Core.Repository
{
    public class DataBaseRepository : IDataBaseRepository
    {
        private MyDbContext _context;
        public DataBaseRepository(MyDbContext context)
        {
            _context = context;
        }

        public List<TableColumn> QueryTable(string table_name)
        {

            string sql = "SELECT  COLUMN_NAME,  COLUMN_TYPE ,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS where   table_name =@table_name";
            var connection = _context.Database.GetDbConnection();
            using (var cmd = connection.CreateCommand())
            {
                _context.Database.OpenConnection();
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.Add(new MySqlParameter("@table_name", table_name));
                var dr = cmd.ExecuteReader();
                var columnSchema = dr.GetColumnSchema();
                var data = new List<TableColumn>();
                while (dr.Read())
                {
                    TableColumn item = new TableColumn();
                    item.COLUMN_NAME = dr["COLUMN_NAME"].ToString();
                    item.DATA_TYPE = dr["DATA_TYPE"].ToString();
                    item.COLUMN_TYPE = dr["COLUMN_TYPE"].ToString();
                    item.COLUMN_COMMENT = dr["COLUMN_COMMENT"].ToString();
                    item.IS_NULLABLE = dr["IS_NULLABLE"].ToString();
                    item.COLUMN_DEFAULT = dr["COLUMN_DEFAULT"].ToString();
                    item.CHARACTER_MAXIMUM_LENGTH = dr["CHARACTER_MAXIMUM_LENGTH"] == null ? (int)dr["CHARACTER_MAXIMUM_LENGTH"] : null;
                    data.Add(item);
                }
                dr.Dispose();
                _context.Database.CloseConnection();
                return data;
            }


        }
        public string queryTablePrimary(string table_name)
        {
            FormattableString sql = $"SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE`   WHERE table_name = {table_name}  AND constraint_name = 'PRIMARY'";
            var list = _context.Database.SqlQuery<string>(sql).ToList();
            return list[0];
        }

        public void ExecuteSql(FormattableString sql)
        {
            _context.Database.ExecuteSql(sql);
        }
    }
}
